package com.servlet.model.database;
/**
 *  C'est la classe qui implement l'interface BaseAcces.
 *  Elle permet de se connecter à la base de données.
 */
import java.sql.Date;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import org.apache.commons.lang3.StringEscapeUtils;
import com.servlet.form.Format;
import com.servlet.model.base.*;

public class BaseAccesImpl implements BaseAcces {
	/** Base de données **/
	Database database;

	public static void main(String[] argv) {
		BaseAccesImpl b = new BaseAccesImpl();
		b.connect();
		//String motPasse = "GL201012";
		//motPasse = MD5.generateCode(motPasse);
		//b.addAdministrator(motPasse, 0.6, 0.4, 0.3);
	}

	public BaseAccesImpl() {
		String url = "jdbc:postgresql://localhost:5432/propig";
		String user = "postgres";
		String passwd = "postgres";

		database = new Database(url, user, passwd);
	}

	//@Override
	public boolean connect() {
		return database.connect();
	}

	//@Override
	public boolean deconnect() {
		return database.deconnect();
	}

	private boolean existArea(Area area) {
		String selectSql = "SELECT * FROM area WHERE hcode=" + area.getHCode();
		String result = database.selectSQL(selectSql);
		if (result != null && result != "")
			return true;
		else
			return false;
	}

	//@Override
	public boolean verifyAccount(String email, String passwd) {
		
		if (Format.verifyEmail(email)) {
			String selectSql = "SELECT * FROM profile WHERE email='" + email
					+ "' AND password='" + passwd + "'";
			String result = database.selectSQL(selectSql);
			if (result != null && result != "")
				return true;
			else
				return false;
		}

		return false;
	}

	//@Override
	public boolean verifyAdmin(String passwd) {
		//Protection contre les injections sql
		passwd = StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(passwd));
		
		String selectSql = "SELECT * FROM administrator WHERE passwd='"
				+ passwd + "'";
		String result = database.selectSQL(selectSql);
		if (result != null && result != "")
			return true;
		else
			return false;

	}

	//@Override
	public String getPasswd(String email) {
		//Protection contre les injections sql
		email = StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(email));
		
		if (Format.verifyEmail(email)) {
			String selectSql = "SELECT password FROM Profile WHERE email='"
					+ email + "'";
			String result = database.selectSQL(selectSql);

			return result;
		}
		return null;
	}

	//@Override
	public boolean modifyPasswd(String email, String oldPasswd, String newPasswd) {
		//Protection contre les injections sql
		newPasswd = StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(newPasswd));
		email = StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(email));
		oldPasswd = StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(oldPasswd));
		
		if (!verifyAccount(email, oldPasswd))
			return false;

		String updateSql = "UPDATE profile SET password='" + newPasswd
				+ "' WHERE email='" + email + "'";

		return database.updateSql(updateSql);
	}

	//@Override
	public boolean addCompte(String email, String passwd, String first_name,
			String last_name, Date birth_day) {

		//Protection contre les injections sql
		email = StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(email));
		passwd = StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(passwd));
		first_name = StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(first_name));
		last_name = StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(last_name));
				
				
		if (Format.verifyEmail(email) && Format.verifyName(first_name)
				&& Format.verifyName(last_name)) {

			String insertSQL;
			if( birth_day != null )
				insertSQL = "INSERT INTO profile VALUES ('" + email + "' ,'"
					+ passwd + "' ,'" + first_name + "' , '" + last_name
					+ "' , '" + birth_day + "')";
			else
				insertSQL = "INSERT INTO profile VALUES ('" + email + "' ,'"
						+ passwd + "' ,'" + first_name + "' , '" + last_name
						+ "')";

			return database.insertSql(insertSQL);
		}
		return false;
	}

	//@Override
	public boolean addNews(String email_user1, String email_user2,
			Date date_publish, int genre) {
		
		//Protection contre les injections sql
		email_user1 = StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(email_user1));
		email_user2 = StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(email_user2));
		
		if (Format.verifyEmail(email_user1) && Format.verifyEmail(email_user2)) {
			News news = new News(email_user1, email_user2, date_publish, genre);
			return database.insertSql(news.generateInsertSQL());
		}

		return false;
	}

	public boolean addNews(String email_user1, String email_user2,
			Date date_publish, int genre, String info_sup) {
		//Protection contre les injections sql
		email_user1 = StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(email_user1));
		email_user2 = StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(email_user2));
		info_sup = StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(info_sup));
				
		if (Format.verifyEmail(email_user1) && Format.verifyEmail(email_user2)) {
			News news = new News(email_user1, email_user2, date_publish, genre,
					info_sup);
			return database.insertSql(news.generateInsertSQL());
		}

		return false;
	}

	//@Override
	public boolean addStudy(String email, String school, Date date_begin,
			Date date_end, String major, int city_code, String country,
			String city) {
		//Protection contre les injections sql
		email = StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(email));
		school = StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(school));
		major = StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(major));
		country = StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(country));
		city = StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(city));
						
				
		boolean flag = true;
		Area area = new Area(city_code, country, city);
		if (!existArea(area)) {

			flag = database.insertSql(area.generateInsertSQL());
		}

		if (!flag)
			return false;

		Study study = new Study(email, school, date_begin, date_end, major,
				area);
		return database.insertSql(study.generateInsertSQL());
	}

	//@Override
	public boolean addExperience(String email, String enterprise,
			Date date_begin, Date date_end, String post,
			Sector activity_domain, String description, int city_code,
			String country, String city) {
		//Protection contre les injections sql
		email = StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(email));
		post = StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(post));
		enterprise = StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(enterprise));
		country = StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(country));
		city = StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(city));
		description = StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(description));
		
		boolean flag = true;
		Area area = new Area(city_code, country, city);
		if (!existArea(area)) {

			flag = database.insertSql(area.generateInsertSQL());
		}

		if (!flag)
			return false;

		Experience experience = new Experience(email, enterprise, date_begin,
				date_end, post, activity_domain, description, area);

		return database.insertSql(experience.generateInsertSQL());
	}

	//@Override
	public boolean addContact(String email_user1, String email_user2,
			Date date_invitation) {
		//Protection contre les injections sql
		email_user1 = StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(email_user1));
		email_user2 = StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(email_user2));
				
		
		Contact contact = new Contact(email_user1, email_user2, false,
				date_invitation, null);
		return database.insertSql(contact.generateInsertSQL());
	}

	//@Override
	public boolean removeNews(News news) {
		return database.deleteSql(news.generateDeleteSQL());
	}

	//@Override
	public boolean removeContact(Contact contact) {
		return database.deleteSql(contact.generateDeleteSQL());
	}

	//@Override
	public boolean removeStudy(Study study) {
		return database.deleteSql(study.generateDeleteSQL());
	}

	//@Override
	public boolean removeExperience(Experience experience) {
		return database.deleteSql(experience.generateDeleteSQL());
	}

	//@Override
	public boolean updateProfil(Profile profile) {
		boolean flag = false;
		String updateSql = "UPDATE profile SET ";
		
		if (profile.getIs_modified_first_name()) {
			if (!Format.verifyName(profile.getFirst_name()))
				return false;
			updateSql += "first_name='" + StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(profile.getFirst_name())) + "' ";
			flag = true;
		}
		if (profile.getIs_modified_last_name()) {
			if (!Format.verifyName(profile.getLast_name()))
				return false;

			if (flag)
				updateSql += ", last_name='" + StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(profile.getLast_name())) + "' ";
			else {
				updateSql += "last_name='" + StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(profile.getLast_name())) + "' ";
				flag = true;
			}
		}
		if (profile.getIs_modified_birth_date() && profile.getBirth_date() != null ) {
			if (flag)
				updateSql += ", birth_date='" + profile.getBirth_date() + "' ";
			else {
				updateSql += " birth_date='" + profile.getBirth_date() + "' ";
				flag = true;
			}
		}
		if (profile.getIs_modified_status()) {
			if (flag)
				updateSql += ", current_status='"
						+ StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(profile.getStatus().getStatus())) + "' ";
			else {
				updateSql += " current_status='"
						+ StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(profile.getStatus().getStatus())) + "' ";
				flag = true;
			}
		}
		if (profile.getIs_modified_photo()) {
			if (flag)
				updateSql += ", photo='" + profile.getPhoto() + "' ";
			else {
				updateSql += " photo='" + profile.getPhoto() + "' ";
				flag = true;
			}
		}

		if (!existArea(profile.getWorldArea())) {
			flag = database.insertSql(profile.getWorldArea()
					.generateInsertSQL());
		}

		if (profile.getIs_modified_world_area()) {
			if (flag)
				updateSql += ", world_area="
						+ profile.getWorldArea().getHCode();
			else {
				updateSql += ", world_area="
						+ profile.getWorldArea().getHCode();
				flag = true;
			}
		}

		if (profile.getIs_modified_nb_contacts_par_page()) {
			if (flag)
				updateSql += ", nb_contacts_par_page='"
						+ profile.getNb_contacts_par_page() + "' ";
			else {
				updateSql += " nb_contacts_par_page='"
						+ profile.getNb_contacts_par_page() + "' ";
				flag = true;
			}
		}

		if (profile.getIs_modified_nb_news_par_page()) {
			if (flag)
				updateSql += ", nb_news_par_page='"
						+ profile.getNb_news_par_page() + "' ";
			else {
				updateSql += " nb_news_par_page='"
						+ profile.getNb_news_par_page() + "' ";
				flag = true;
			}
		}

		updateSql += " WHERE email = '" + StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(profile.getEmail())) + "'";

		System.out.println(updateSql);
		
		flag = database.updateSql(updateSql);
		if (flag) {
			profile.setIs_modified_birth_date(false);
			profile.setIs_modified_first_name(false);
			profile.setIs_modified_last_name(false);
			profile.setIs_modified_photo(false);
			profile.setIs_modified_status(false);
			profile.setIs_modified_world_area(false);
			profile.setIs_modified_nb_contacts_par_page(false);
			profile.setIs_modified_nb_news_par_page(false);
		}

		// System.out.println(updateSql);

		return flag;
	}

	//@Override
	public boolean updateStudy(Study study) {
		boolean flag = false;
		String updateSql = "UPDATE study SET ";
		String whereSql = " WHERE email = '" + StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(study.getEmail())) + "'";
		if (study.getIs_modified_school()) {
			updateSql += "school='" + StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(study.getSchool())) + "' ";
			flag = true;
		} else
			whereSql += " AND school='" + StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(study.getSchool())) + "' ";

		if (study.getIs_modified_date_begin()) {
			if (flag)
				updateSql += ", date_begin='" + study.getDateBegin() + "' ";
			else {
				updateSql += " date_begin='" + study.getDateBegin() + "' ";
				flag = true;
			}
		} else
			whereSql += " AND date_begin='" + study.getDateBegin() + "' ";

		if (study.getIs_modified_date_end()) {
			if (flag)
				updateSql += ", date_end='" + study.getDateEnd() + "' ";
			else {
				updateSql += " date_end='" + study.getDateEnd() + "' ";
				flag = true;
			}
		} else
			whereSql += " AND date_end='" + study.getDateEnd() + "' ";

		if (study.getIs_modified_major()) {
			if (flag)
				updateSql += ", major='" + StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(study.getMajor())) + "' ";
			else {
				updateSql += " major='" + StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(study.getMajor())) + "' ";
				flag = true;
			}
		} else
			whereSql += " AND major='" + StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(study.getMajor())) + "' ";

		if (study.getIs_modified_area()) {
			if (flag)
				updateSql += ", world_area=" + study.getArea().getHCode();
			else {
				updateSql += " world_area=" + study.getArea().getHCode();
				flag = true;
			}
		} else
			whereSql += " AND world_area=" + study.getArea().getHCode();

		updateSql += whereSql;

		flag = database.updateSql(updateSql);
		if (flag) {
			study.setIs_modified_area(false);
			study.setIs_modified_date_begin(false);
			study.setIs_modified_date_end(false);
			study.setIs_modified_major(false);
			study.setIs_modified_school(false);
		}

		return flag;
	}

	//@Override
	public boolean updateExperience(Experience experience) {
		boolean flag = false;
		String updateSql = "UPDATE Experience SET ";
		String whereSql = " WHERE email = '" + StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(experience.getEmail())) + "'";
		if (experience.getIs_modified_enterprise()) {
			updateSql += "enterprise='" + StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(experience.getEnterprise())) + "' ";
			flag = true;
		} else
			whereSql += " AND enterprise='" + StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(experience.getEnterprise())) + "' ";

		if (experience.getIs_modified_date_begin()) {
			if (flag)
				updateSql += ", date_begin='" + experience.getDateBegin()
						+ "' ";
			else {
				updateSql += " date_begin='" + experience.getDateBegin() + "' ";
				flag = true;
			}
		} else
			whereSql += " AND date_begin='" + experience.getDateBegin() + "' ";

		if (experience.getIs_modified_date_end()) {
			if (flag)
				updateSql += ", date_end='" + experience.getDateEnd() + "' ";
			else {
				updateSql += " date_end='" + experience.getDateEnd() + "' ";
				flag = true;
			}
		} else
			whereSql += " AND date_end='" + experience.getDateEnd() + "' ";

		if (experience.getIs_modified_area()) {
			if (flag)
				updateSql += ", world_area=" + experience.getArea().getHCode();
			else {
				updateSql += " world_area=" + experience.getArea().getHCode();
				flag = true;
			}
		} else
			whereSql += " AND world_area=" + experience.getArea().getHCode();

		if (experience.getIs_modified_activity_domain()) {
			if (flag)
				updateSql += ", activity_domain='"
						+ experience.getActivityDomain().getSector() + "' ";
			else {
				updateSql += " activity_domain='"
						+ experience.getActivityDomain().getSector() + "' ";
				flag = true;
			}
		} else
			whereSql += " AND activity_domain='"
					+ experience.getActivityDomain().getSector() + "' ";

		if (experience.getIs_modified_descriptionn()) {
			if (flag)
				updateSql += ", description='" + StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(experience.getDescription()))
						+ "' ";
			else {
				updateSql += " description='" + StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(experience.getDescription()))
						+ "' ";
				flag = true;
			}
		} else
			whereSql += " AND description='" + StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(experience.getDescription()))
					+ "' ";

		if (experience.getIs_modified_post()) {
			if (flag)
				updateSql += ", post='" + StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(experience.getPost())) + "' ";
			else {
				updateSql += " post='" + StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(experience.getPost())) + "' ";
				flag = true;
			}
		} else
			whereSql += " AND post='" + StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(experience.getPost())) + "' ";

		updateSql += whereSql;

		flag = database.updateSql(updateSql);
		if (flag) {
			experience.setIs_modified_activity_domain(false);
			experience.setIs_modified_area(false);
			experience.setIs_modified_date_begin(false);
			experience.setIs_modified_date_end(false);
			experience.setIs_modified_descriptionn(false);
			experience.setIs_modified_enterprise(false);
			experience.setIs_modified_post(false);
		}

		return flag;
	}

	//@Override
	public boolean updateContact(Contact contact) {
		boolean flag = false;
		String updateSql = "UPDATE Contact SET ";
		String whereSql = " WHERE email_user_1 = '" + StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(contact.getEmail_user1()))
				+ "' AND email_user_2 = '" + StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(contact.getEmail_user2())) + "'";
		if (contact.getIs_modified_accepted()) {
			updateSql += "is_accepted=" + contact.getIs_accepted();
			flag = true;
		}

		if (contact.getIs_modified_date_acceptation()) {
			if (flag)
				updateSql += ", date_acceptation='"
						+ contact.getDate_acceptation() + "' ";
			else {
				updateSql += " date_acceptation='"
						+ contact.getDate_acceptation() + "' ";
				flag = true;
			}
		}

		if (contact.getIs_modified_date_invitation()) {
			if (flag)
				updateSql += ", date_invitation='"
						+ contact.getDate_invitation() + "' ";
			else {
				updateSql += " date_invitation='"
						+ contact.getDate_invitation() + "' ";
				flag = true;
			}
		}
		updateSql += whereSql;

		flag = database.updateSql(updateSql);
		if (flag) {
			contact.setIs_modified_accepted(false);
			contact.setIs_modified_date_acceptation(false);
			contact.setIs_modified_date_invitation(false);
		}

		return flag;
	}

	//@Override
	public Area findArea(int city_code) {
		return database.findArea(city_code);
	}

	public boolean existEmail(String email) {
		return database.existEmail(email);
	}

	//@Override
	public Profile findProfile(String email) {
		return database.findProfile(email);
	}

	//@Override
	public ArrayList<String> findEmailByName(String name) {
		return database.findEmailByName(name);
	}

	//@Override
	public ArrayList<String> findEmailByEnterprise(String enterprise) {
		return database.findEmailByEnterprise(enterprise);
	}

	//@Override
	public ArrayList<String> findEmailBySchool(String school) {
		return database.findEmailBySchool(school);
	}

	//@Override
	public ArrayList<String> findEmailBySchoolTool(String email) {
		return database.findEmailBySchoolTool(email);
	}

	//@Override
	public ArrayList<String> findEmailByEntrepriseTool(String email) {
		return database.findEmailByEntrepriseTool(email);
	}

	//@Override
	public ArrayList<Study> findStudies(String email) {
		return database.findStudies(email);
	}

	//@Override
	public ArrayList<Experience> findExperiences(String email) {
		return database.findExperiences(email);
	}

	//@Override
	public ArrayList<Contact> findContacts(String email) {
		return database.findContacts(email);
	}

	//@Override
	public ArrayList<News> findNews(String email) {
		return database.findNews(email);
	}

	//@Override
	public ArrayList<Study> findStudiesByMajor(String major) {
		return database.findStudiesByMajor(major);
	}

	//@Override
	public Collection<String> findEveryEmail() {
		return database.findEveryEmail();
	}

	//@Override
	public boolean addAdministrator(String passwd, double coefPro,
			double coefProfile, double coefStudy) {
		//Protection contre les injections sql
		passwd = StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(passwd));
				
		Administrator administrator = new Administrator(passwd, coefPro,
				coefProfile, coefStudy);
		return database.insertSql(administrator.generateInsertSQL());

	}

	//@Override
	public boolean existPasswd(String passwd) {
		return database.existPasswd(passwd);
	}

	//@Override
	public boolean modifyPasswdAdmin(String oldPasswd, String newPasswd) {
		//Protection contre les injections sql
		oldPasswd = StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(oldPasswd));
		newPasswd = StringEscapeUtils.escapeEcmaScript(StringEscapeUtils.escapeHtml4(newPasswd));
		
		String updateSql = "UPDATE Administrator SET password='" + newPasswd
				+ "' WHERE passwd='" + oldPasswd + "'";

		return database.updateSql(updateSql);
	}

	//@Override
	public boolean updateAdministrator(Administrator admin) {
		boolean flag = false;
		String updateSql = "UPDATE Administrator SET ";
		String whereSql = " ";

		if (admin.getIs_modified_coefPro()) {
			updateSql += "coefpro=" + admin.getCoefPro();
			flag = true;
		}

		if (admin.getIs_modified_coefProfile()) {
			if (flag)
				updateSql += ", coefprofile=" + admin.getCoefProfile();
			else {
				updateSql += "coefprofile=" + admin.getCoefProfile();
				flag = true;
			}
		}

		if (admin.getIs_modified_coefStudy()) {
			if (flag)
				updateSql += ", coefstudy=" + admin.getCoefStudy();
			else {
				updateSql += "coefstudy=" + admin.getCoefStudy();
				flag = true;
			}
		}
		updateSql += whereSql;

		flag = database.updateSql(updateSql);
		if (flag) {
			admin.setIs_modified_coefPro(false);
			admin.setIs_modified_coefStudy(false);
			admin.setIs_modified_coefProfile(false);
		}

		return flag;
	}

	//@Override
	public boolean removeAdmin(Administrator admin) {
		return database.deleteSql(admin.generateDeleteSQL());
	}

	//@Override
	public Administrator findAdmin(String passwd) {
		return database.findAdmin(passwd);
	}
	
	public Administrator findAdmin() {
		return database.findAdmin();
	}

	//@Override
	public ArrayList<String> findEmailBySpecialite(String specialite) {
		return database.findEmailBySpecialite(specialite);
	}

	public HashMap<String, String> getPasswdName(String email) {
		return database.getPasswdName(email);
	}

	public ArrayList<String> getFieldOfTable(String table, String field, String word )
	{
		return database.getFieldOfTable( table,  field, word );
	}
}
